<?php
/*
 * @Author: your name
 * @Date: 2022-01-20 16:26:40
 * @LastEditTime: 2022-03-10 15:39:00
 * @LastEditors: Please set LastEditors
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: /nlog/app/Models/crm_model/CrmLinkman.php
 */

namespace App\Models\crm_model;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class CrmCustomer extends Model
{
    use HasFactory;
    static string $tableName = 'crm_customer';
    
   
    /**
     * @description: 
     * @param {*} $admins_id       admins表id      所属联系人
     * @param {*} $division_id     division表id    所属部门id
     * @param {*} $division_name   自己的部门名称 
     * @param {*} $whereStr
     * @param {*} $limitStr
     * @return {*}
     */     
    public static function crmCustomerList($admins_id,$division_id,$division_name,$whereStr = "" ,$limitStr = ""){
       
       
       
       
        // return DB::select( " SELECT `crm_customer`.*, `admins`.`nickName`, `admins`.`realName`, `chain`.`division_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id`, CASE WHEN `division`.`name` IS NULL THEN '全公司' ELSE `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division` ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_customer` ON `crm_customer`.`division_id` = `chain`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id` WHERE `crm_customer`.`delete_time` IS NULL ".$whereStr." ORDER BY `id` DESC ".$limitStr);
        // echo " SELECT `crm_customer`.*,`admins`.`nickName`,`admins`.`realName` FROM (SELECT `crm_customer`.*,`division`.`name` as `division_name` FROM `crm_customer`  INNER JOIN      ( SELECT `id`,`name` FROM `crm_division` WHERE `pid` = ".$division_id." ) as `division` ON `division`.`id` = `crm_customer`.`division_id`  UNION ALL SELECT *,'".$division_name."' as `division_name` FROM  `crm_customer`   WHERE `admins_id` = ".$admins_id." ) as `crm_customer` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id` WHERE `crm_customer`.`delete_time` is null AND `crm_customer`.`in_pool` = 0 ".$whereStr." GROUP BY `crm_customer`.`id` ORDER BY `id` DESC ".$limitStr;
        return DB::select( " SELECT `crm_customer`.*,`admins`.`nickName`,`admins`.`realName` FROM (SELECT `crm_customer`.*,`division`.`name` as `division_name` FROM `crm_customer`  INNER JOIN      ( SELECT `id`,`name` FROM `crm_division` WHERE `pid` = ".$division_id." ) as `division` ON `division`.`id` = `crm_customer`.`division_id`  UNION ALL SELECT *,'".$division_name."' as `division_name` FROM  `crm_customer`   WHERE `admins_id` = ".$admins_id." ) as `crm_customer` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id` WHERE `crm_customer`.`delete_time` is null AND `crm_customer`.`in_pool_time` is  null ".$whereStr." GROUP BY `crm_customer`.`id` ORDER BY `id` DESC ".$limitStr);
    }
    

    public static function crmCustomerCount($admins_id,$division_id,$division_name,$whereStr = ""){
       
        // return DB::select("  SELECT COUNT(*) as `count` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id`, CASE WHEN `division`.`name` IS NULL THEN '全公司' ELSE `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division` ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_customer` ON `crm_customer`.`division_id` = `chain`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id` WHERE `crm_customer`.`delete_time` IS NULL ".$whereStr);
    //    echo " SELECT COUNT(*) as `count` ( SELECT `crm_customer`.*,`admins`.`nickName`,`admins`.`realName` FROM (SELECT `crm_customer`.*,`division`.`name` as `division_name` FROM `crm_customer`  INNER JOIN      ( SELECT `id`,`name` FROM `crm_division` WHERE `pid` = ".$division_id." ) as `division` ON `division`.`id` = `crm_customer`.`division_id`  UNION ALL SELECT *,'".$division_name."' as `division_name` FROM  `crm_customer`   WHERE `admins_id` = ".$admins_id." ) as `crm_customer` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id` WHERE `crm_customer`.`delete_time` is null AND `crm_customer`.`in_pool` = 0 ".$whereStr." GROUP BY `crm_customer`.`id` ) as `all`  ";
        return DB::select( " SELECT COUNT(*) as `count` FROM ( SELECT `crm_customer`.*,`admins`.`nickName`,`admins`.`realName` FROM (SELECT `crm_customer`.*,`division`.`name` as `division_name` FROM `crm_customer`  INNER JOIN      ( SELECT `id`,`name` FROM `crm_division` WHERE `pid` = ".$division_id." ) as `division` ON `division`.`id` = `crm_customer`.`division_id`  UNION ALL SELECT *,'".$division_name."' as `division_name` FROM  `crm_customer`   WHERE `admins_id` = ".$admins_id." ) as `crm_customer` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id` WHERE `crm_customer`.`delete_time` is null AND `crm_customer`.`in_pool_time` is  null  ".$whereStr." GROUP BY `crm_customer`.`id` ) as `all`  ");
       
    }

    public static function customerPoolList($division_id,$whereStr = '',$limitStr = ''){
        return DB::select( " SELECT `crm_customer`.*,`admins`.`nickName`,`admins`.`realName` FROM ( SELECT `crm_customer`.*, `division`.`name` AS `division_name` FROM `crm_customer` INNER JOIN ( SELECT `id`, `name` FROM `crm_division` WHERE `id` = ".$division_id." OR  `pid` = ".$division_id." ) AS `division` ON `division`.`id` = `crm_customer`.`division_id` ) as `crm_customer` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id` WHERE `crm_customer`.`delete_time` IS NULL AND `crm_customer`.`in_pool_time` IS NOT NULL ".$whereStr." ORDER BY `crm_customer`.`in_pool_time` DESC ".$limitStr);
    }

    public static function customerPoolCount($division_id,$whereStr = ''){
        return DB::select( " SELECT COUNT(*) as `count` FROM ( SELECT `crm_customer`.*, `division`.`name` AS `division_name` FROM `crm_customer` INNER JOIN ( SELECT `id`, `name` FROM `crm_division` WHERE `id` = ".$division_id." OR  `pid` = ".$division_id." ) AS `division` ON `division`.`id` = `crm_customer`.`division_id` ) as `crm_customer` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id` WHERE `crm_customer`.`delete_time` IS NULL AND `crm_customer`.`in_pool_time` IS NOT NULL ".$whereStr  );
        
    }


    public static function customerApplyList($division_id,$whereStr = '',$limitStr = ''){
        // echo " SELECT `transfer_apply_for`.*,`crm_customer`.*,`admins`.`nickName`,  `admins`.`realName`  FROM `transfer_apply_for` INNER  JOIN `crm_customer` ON `crm_customer`.`id` = `transfer_apply_for`.`customer_id` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id`    WHERE `transfer_apply_for`.`division_id` = $division_id ".$whereStr."  ORDER BY `id` DESC ".$limitStr;
        // echo " SELECT `transfer_apply_for`.*,`crm_customer`.*,`admins`.`nickName`,  `admins`.`realName`  FROM `transfer_apply_for` INNER  JOIN `crm_customer` ON `crm_customer`.`id` = `transfer_apply_for`.`customer_id` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id`    WHERE `transfer_apply_for`.`division_id` = $division_id ".$whereStr."  ORDER BY `transfer_apply_for`.`id` DESC ".$limitStr;
        // echo " SELECT `crm_customer`.*,`transfer_apply_for`.id,`transfer_apply_for`.`remark` as `transfer_apply_for_remark`,`transfer_apply_for`.`status` as `transfer_apply_for_status`,`afterAdmins`.`nickName` as `afterAdmins_nickName`,`afterAdmins`.`realName` as `afterAdmins_realName`,`crm_division`.`name` as `division_name`,`admins`.`nickName`,  `admins`.`realName`  FROM `transfer_apply_for` INNER  JOIN `crm_customer` ON `crm_customer`.`id` = `transfer_apply_for`.`customer_id` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id`  INNER JOIN `crm_division` ON `crm_customer`.`division_id` = `crm_division`.`id` INNER JOIN `admins` as `afterAdmins` ON `afterAdmins`.`id` = `transfer_apply_for`.`after_admins_id`   WHERE `transfer_apply_for`.`division_id` = $division_id ".$whereStr."  ORDER BY `transfer_apply_for`.`id` DESC ".$limitStr;
        return DB::select( " SELECT `crm_customer`.*,`before_admins`.`nickName` as `before_admins_nickName`,`before_admins`.`realName` as `before_admins_realName`,`transfer_apply_for`.`after_division_id`,`transfer_apply_for`.`before_admins_id`,`transfer_apply_for`.`after_admins_id` ,`crm_customer`.`id` as `crm_customer_id`,`transfer_apply_for`.id,`transfer_apply_for`.`remark` as `transfer_apply_for_remark`,`transfer_apply_for`.`status` as `transfer_apply_for_status`,`afterAdmins`.`nickName` as `afterAdmins_nickName`,`afterAdmins`.`realName` as `afterAdmins_realName`,`crm_division`.`name` as `division_name`,`admins`.`nickName`,  `admins`.`realName`  FROM `transfer_apply_for` LEFT  JOIN `admins` as `before_admins`  ON `before_admins`.`id` = `transfer_apply_for`.`before_admins_id` INNER  JOIN `crm_customer` ON `crm_customer`.`id` = `transfer_apply_for`.`customer_id` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id`  INNER JOIN `crm_division` ON `crm_customer`.`division_id` = `crm_division`.`id` INNER JOIN `admins` as `afterAdmins` ON `afterAdmins`.`id` = `transfer_apply_for`.`after_admins_id`   WHERE `transfer_apply_for`.`division_id` = $division_id ".$whereStr."  ORDER BY `transfer_apply_for`.`id` DESC ".$limitStr);
    }
    public static function customerApplyCount($division_id,$whereStr = ''){

        return DB::select( " SELECT COUNT(*) as `count`  FROM `transfer_apply_for` INNER  JOIN `crm_customer` ON `crm_customer`.`id` = `transfer_apply_for`.`customer_id` INNER JOIN `admins` ON `admins`.`id` = `crm_customer`.`admins_id`  INNER JOIN `crm_division` ON `crm_customer`.`division_id` = `crm_division`.`id` INNER JOIN `admins` as `afterAdmins` ON `afterAdmins`.`id` = `transfer_apply_for`.`after_admins_id`   WHERE `transfer_apply_for`.`division_id` = $division_id ".$whereStr);

    }


    public static function customerLinkManEdit($linkmanId,$idStr){
        // echo " UPDATE  `crm_customer` SET `linkman_id` = IF(`linkman_id` = '' OR `linkman_id` is NULL, '".$linkmanId."' ,CONCAT(`linkman_id`,',".$linkmanId."'))  WHERE `id` IN (".$idStr.") AND FIND_IN_SET('".$linkmanId."',IFNULL(`linkman_id`,''))  = 0  ";
        return DB::update(" UPDATE  `crm_customer` SET `linkman_id` = IF(`linkman_id` = '' OR `linkman_id` is NULL, '".$linkmanId."' ,CONCAT(`linkman_id`,',".$linkmanId."'))  WHERE `id` IN (".$idStr.") AND FIND_IN_SET('".$linkmanId."',IFNULL(`linkman_id`,''))  = 0  "); 
    }

    public static function customerChangeOld($linkmanId){
        return DB::update("UPDATE  `crm_customer` SET  `linkman_id` = TRIM(BOTH ',' FROM   REPLACE(CONCAT(',',`linkman_id`,','),',".$linkmanId.",','')) WHERE FIND_IN_SET('".$linkmanId."',`linkman_id`) > 0");
    }

    public static function customerFindInSet($linkmanId){
        return DB::select(" SELECT `id` FROM  `crm_customer`  WHERE FIND_IN_SET('".$linkmanId."',`linkman_id`) > 0");
    }
   

    public static function customerLinkmanId($linkmanId){
        return DB::select( " SELECT `id` FROM ".self::$tableName." WHERE FIND_IN_SET(".$linkmanId.",`linkman_id`)  > 0 ");
    }
   
   
}

